package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.CarStyleCountDto;
import com.tianqu.tms.dto.MarketCarPriceCountDto;
import com.tianqu.tms.dto.VendorDto;

public class VendorDaoImp extends TmsDao implements VendorDao {

	protected VendorDaoImp() throws TmsDaoException {
		super();
	}
	
	
	@Override
	public List<VendorDto> findAllVendorInfo() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		VendorDto dto = null;
		List<VendorDto> list = new ArrayList<VendorDto>();
		
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID        	,");
			sql.append("VENDORNAME,  ");
			sql.append("COUNTRY,  ");
			sql.append("FIRSTCHAR ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_VENDOR);

			ps = conn.prepareStatement(sql.toString());
			
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new VendorDto();
				dto.setVendorId(rs.getString("ID"));
				dto.setVendorName(rs.getString("VENDORNAME"));
				dto.setCountry(rs.getString("COUNTRY"));
				dto.setFirstChar(rs.getString("FIRSTCHAR"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}

	@Override
	public VendorDto getVendorInfo(String id) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		VendorDto dto = new VendorDto();
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("ID        	,");
			sql.append("VENDORNAME,  ");
			sql.append("COUNTRY,  ");
			sql.append("FIRSTCHAR ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_VENDOR);
			sql.append(" WHERE ID = ?");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, id);
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new VendorDto();
				dto.setVendorId(rs.getString("ID"));
				dto.setVendorName(rs.getString("VENDORNAME"));
				dto.setCountry(rs.getString("COUNTRY"));
				dto.setFirstChar(rs.getString("FIRSTCHAR"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return dto;
	}


	public int findAllVendorInfoCountTotal() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("	SELECT ");
			sql.append("	COUNT(B.ID) as TOTAL ");
			sql.append("FROM ");
			sql.append("	"+TmsTableNames.TMS_MARKET_CARVENDOR_COUNT+" A, ");
			sql.append("	"+TmsTableNames.TMS_VENDOR+" B ");
			sql.append("WHERE ");
			sql.append("	A.VENDORID = B.ID ");
			sql.append("AND A.ONLINECOUNT > 0 ");
			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt("TOTAL");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return 0;
	
	}
	
	@Override
	public List<VendorDto> findAllVendorInfoCount() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		VendorDto dto = null;
		List<VendorDto> list = new ArrayList<VendorDto>();
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("	SELECT ");
			sql.append("	B.ID, ");
			sql.append("	B.VENDORNAME, ");
			sql.append("	B.COUNTRY, ");
			sql.append("	B.FIRSTCHAR, ");
			sql.append("  A.OFFLINECOUNT, ");
			sql.append("  A.ONLINECOUNT, ");
			sql.append("  B.ENIMAGE ");
			sql.append("FROM ");
			sql.append("	"+TmsTableNames.TMS_MARKET_CARVENDOR_COUNT+" A, ");
			sql.append("	"+TmsTableNames.TMS_VENDOR+" B ");
			sql.append("WHERE ");
			sql.append("	A.VENDORID = B.ID ");
			sql.append("AND A.ONLINECOUNT > 0 ");
			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new VendorDto();
				dto.setVendorId(rs.getString("ID"));
				dto.setVendorName(rs.getString("VENDORNAME"));
				dto.setCountry(rs.getString("COUNTRY"));
				dto.setFirstChar(rs.getString("FIRSTCHAR"));
				dto.setOfflineCount(rs.getInt("OFFLINECOUNT"));
				dto.setOnlineCount(rs.getInt("ONLINECOUNT"));
				dto.setEnImage(rs.getString("ENIMAGE"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}
   
	@Override
	public List<CarStyleCountDto> getCarStyleCount(String vendorId) throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		CarStyleCountDto dto = null;
		List<CarStyleCountDto> list = new ArrayList<CarStyleCountDto>();
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("	SELECT ");
			sql.append("	A.VENDORID, ");
			sql.append("	A.BRANDID, ");
			sql.append("	A.BRANDNAME, ");
			sql.append("	A.CARSTYLEID, ");
			sql.append("	A.STYLENAME, ");
			sql.append("	A.ONLINECOUNT, ");
			sql.append("	A.OFFLINECOUNT, ");
			sql.append("	B.EXHAUST, ");
			sql.append("	B.GEARBOX, ");
			sql.append("	A.ONLINELOWPRICE/10000 AS 'ONLINELOWPRICE', ");
			sql.append("	A.ONLINEHIGHPRICE/10000 AS 'ONLINEHIGHPRICE', ");
			sql.append("	A.OFFLINELOWPRICE/10000 AS 'OFFLINELOWPRICE', ");
			sql.append("	A.OFFLINEHIGHPRICE/10000 AS 'OFFLINEHIGHPRICE'");
			sql.append("FROM " + TmsTableNames.TMS_MARKET_CARSTYLE_COUNT +" A LEFT JOIN "+TmsTableNames.TMS_CAR_STYLE +" B");
			sql.append(" ON (A.CARSTYLEID = B.ID) ");
			sql.append(" WHERE A.VENDORID=? AND A.ONLINECOUNT > 0  ORDER BY A.CARSTYLEID ASC");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1,vendorId);
			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new CarStyleCountDto();
				dto.setVendorId(rs.getString("VENDORID"));
				dto.setBrandId(rs.getString("BRANDID")); 
				dto.setBrandName(rs.getString("BRANDNAME"));
				dto.setCarStyleId(rs.getString("CARSTYLEID"));
				dto.setStyleName(rs.getString("STYLENAME"));
				dto.setExhaust(rs.getString("EXHAUST"));
				int gearbox=rs.getInt("GEARBOX");
				if(gearbox == 0){ dto.setGearbox("MT");
				}else if (gearbox == 1) { dto.setGearbox("AT");
				}else if (gearbox == 2) { dto.setGearbox("A/MT");
				}else if (gearbox == 3) { dto.setGearbox("无极变速");
				}else if (gearbox == 4) { dto.setGearbox("双离合"); }
				dto.setStyleName(rs.getString("STYLENAME"));
				dto.setOnlineCount(rs.getInt("ONLINECOUNT"));
				dto.setOnlineLowPrice(rs.getInt("ONLINELOWPRICE"));
				dto.setOnlineHighPrice(rs.getInt("ONLINEHIGHPRICE"));
				dto.setOfflineCount(rs.getInt("OFFLINECOUNT"));
				dto.setOfflineLowPrice(rs.getInt("OFFLINELOWPRICE"));
				dto.setOfflineHighPrice(rs.getInt("OFFLINEHIGHPRICE"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}


	@Override
	public int findOnlineCount() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("	SELECT ");
			sql.append("	SUM(ONLINECOUNT) TOTAL ");
			sql.append("FROM ");
			sql.append("	"+TmsTableNames.TMS_MARKET_CARVENDOR_COUNT);
			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt("TOTAL");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return 0;
	}


	@Override
	public List<VendorDto> findVendorOrderByFirstChar() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		VendorDto dto = null;
		List<VendorDto> list = new ArrayList<VendorDto>();
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT A.VENDORID,A.VENDORNAME,A.FIRSTCHAR,B.ENIMAGE,A.ONLINECOUNT,A.OFFLINECOUNT,A.UPDATETIME ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_MARKET_CARVENDOR_COUNT+" A LEFT JOIN "+TmsTableNames.TMS_VENDOR +" B");
			sql.append(" ON(A.VENDORID = B.ID) WHERE A.ONLINECOUNT > 0");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new VendorDto();
				dto.setVendorId(rs.getString("VENDORID"));
				dto.setVendorName(rs.getString("VENDORNAME"));
				dto.setFirstChar(rs.getString("FIRSTCHAR"));
				dto.setEnImage(rs.getString("ENIMAGE"));
				dto.setOnlineCount(rs.getInt("ONLINECOUNT"));
				dto.setOfflineCount(rs.getInt("OFFLINECOUNT"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}

	@Override
	public List<MarketCarPriceCountDto> getMarketCarPriceCount() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MarketCarPriceCountDto dto = null;
		List<MarketCarPriceCountDto> list = new ArrayList<MarketCarPriceCountDto>();
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT ID,PRICENAME,PRICETYPE,PRICECOUNT FROM "+TmsTableNames.TMS_MARKET_CARPRICE_COUNT);
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new MarketCarPriceCountDto();
				dto.setId(rs.getInt("ID"));
				dto.setPriceCount(rs.getInt("PRICECOUNT"));
				dto.setPriceName(rs.getString("PRICENAME"));
				dto.setPriceType(rs.getInt("PRICETYPE"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}
}
